import datetime
import os
import re

import openpyxl
import win32com.client
from openpyxl.styles import PatternFill, Alignment, Font
from openpyxl.utils import get_column_letter
from tqdm import tqdm

import tools_pmc

import win32con
import win32ui

import warnings

warnings.filterwarnings('ignore')


def 加载_自检程序():
    # date_month = datetime.datetime.today().month
    # temp = input("选择工作月份（默认为" + str(date_month) + "月）：")
    # if temp != "":
    #     date_month = int(temp)
    # else:
    #     print(str(date_month))

    # root_path = r"\\192.168.70.101\19计划管理部\01.计划\3，物料需求\3，每月追料\2.每月物料需求"
    # all_files = tools_pmc.路径内全部文件(root_path)
    # dirt_可选需求算料情况表 = {}
    # a = 1
    # for i in all_files:
    #     if "成品BOM信息表" in i and "需求算料情况" in i and "$" not in i:
    #         print(str(a) + ":" + i + "    修改时间：" +
    #               datetime.datetime.fromtimestamp(os.path.getmtime(i)).strftime("%Y-%m-%d %H:%M:%S"))
    #         dirt_可选需求算料情况表[a] = i
    #         a += 1
    # 需求算料情况表编号 = 0
    # while 需求算料情况表编号 not in dirt_可选需求算料情况表:
    #     需求算料情况表编号 = int(input("请选择“需求算料情况表”编号："))
    print("需求算料情况表合并程序v1.1")
    open_flags = win32con.OFN_FILEMUSTEXIST | win32con.OFN_PATHMUSTEXIST
    fspec = "BOM信息表 (*.xlsx)|*.xlsx||"
    dlg = win32ui.CreateFileDialog(1, None, None, open_flags, fspec)  # 1表示打开文件对话框
    dlg.SetOFNTitle("请选择数据源表")
    dlg.DoModal()
    数据源表路径 = dlg.GetPathName().replace('\\', "/")

    open_flags = win32con.OFN_FILEMUSTEXIST | win32con.OFN_PATHMUSTEXIST
    fspec = "BOM信息表 (*.xlsx)|*.xlsx||"
    dlg = win32ui.CreateFileDialog(1, None, None, open_flags, fspec)  # 1表示打开文件对话框
    dlg.SetOFNTitle("请选择新版底表")
    dlg.DoModal()
    新版底表路径 = dlg.GetPathName().replace('\\', "/")

    print("\n运行配置：")
    # print("工作月份：" + str(date_month))
    print("数据源表路径：" + 数据源表路径)
    print("新版底表路径：" + 新版底表路径)

    return 数据源表路径, 新版底表路径


def main(数据源表路径, 新版底表路径):
    print("\n开始运行:")
    wb_源 = openpyxl.load_workbook(数据源表路径)
    ws_源 = wb_源.worksheets[0]
    dict_列标_源 = {}
    for i in range(1, ws_源.max_column + 1):
        if ws_源.cell(2, i).value not in dict_列标_源:
            dict_列标_源[ws_源.cell(2, i).value] = i
        elif type(ws_源.cell(2, i).value) != int:
            print("重复列标：" + str(ws_源.cell(2, i).value) + "，请检查！")
            exit()
    a_源 = 0
    b_源 = 0
    for i in dict_列标_源:
        if "可用数量" in str(i):
            a_源 = dict_列标_源[i]
        if "缺料统计" in str(i):
            b_源 = dict_列标_源[i]

    wb = openpyxl.load_workbook(新版底表路径)
    ws = wb.worksheets[0]
    dict_列标 = {}
    for i in range(1, ws.max_column + 1):
        if ws.cell(2, i).value not in dict_列标:
            dict_列标[ws.cell(2, i).value] = i
        elif type(ws.cell(2, i).value) != int:
            print("重复列标：" + str(ws_源.cell(2, i).value) + "，请检查！")
            exit()
    a = 0  # 暂时用不上
    b = 0
    for i in dict_列标:
        if "可用数量" in str(i):
            a = dict_列标[i]
        if "缺料统计" in str(i):
            b = dict_列标[i]

    # print(list(dict_列标_源.keys())[b_源])
    # print(list(dict_列标_源.keys())[a_源])
    dict_缺失列 = {}

    # for i in list(dict_列标_源.keys())[1:a_源] + list(dict_列标_源.keys())[b_源 + 2:]:
    #     if i not in dict_列标 and i < b:
    #         print("缺失列：" + str(i))
    #         list_缺失列1.append(i)
    #     elif i not in dict_列标 and i > b:
    #         print("缺失列：" + str(i))
    #         list_缺失列2.append(i - b)

    for i in list(dict_列标_源.keys())[1:a_源]:
        if i not in dict_列标:
            print("缺失列：" + str(i))
            dict_缺失列[dict_列标_源[i]] = dict_列标_源[i]
    temp = len(dict_缺失列)
    for i in list(dict_列标_源.keys())[list(dict_列标_源.values()).index(b_源) + 2:]:
        if i not in dict_列标:
            print("缺失列：" + str(i))
            dict_缺失列[dict_列标_源[i]] = dict_列标_源[i] - b_源 + b+ temp

    excel = win32com.client.DispatchEx('Excel.application')  # 独立运行
    # excel = win32com.client.gencache.EnsureDispatch('Excel.Application')  @
    excel.Visible = False
    excel.DisplayAlerts = False

    print("\n")

    excel_wb = excel.Workbooks.Open(新版底表路径)
    excel_ws = excel_wb.ActiveSheet
    for i in tqdm(dict_缺失列, desc="新建缺失列"):
        excel_ws.Columns(dict_缺失列[i]).Insert()

    excel_wb.SaveAs(tools_pmc.lj_temp)
    excel_wb.Close()
    excel.Quit()

    table_array_源 = list(ws_源['A2': get_column_letter(ws_源.max_column) + str(ws_源.max_row)])

    wb = openpyxl.load_workbook(tools_pmc.lj_temp)
    ws = wb.worksheets[0]
    for i in tqdm(dict_缺失列, desc="缺失列数据填入"):
        ws.cell(1, dict_缺失列[i]).value = ws_源.cell(1, i).value
        ws.cell(2, dict_缺失列[i]).fill = ws_源.cell(2, i).fill.copy(
            source=ws_源.cell(2, i).fill)
        ws.cell(2, dict_缺失列[i]).font = ws_源.cell(2, i).font.copy(
            source=ws_源.cell(2, i).font)
        for j in range(2, ws.max_row + 1):
            temp = tools_pmc.vlookup(ws.cell(j, 1).value, table_array_源, i)
            if temp is None or temp == "":
                if str(ws.cell(j - 1, dict_缺失列[i]).value)[0] == "=":
                    ws.cell(j, dict_缺失列[i]).value = re.sub(r'\d+', str(j), ws.cell(j - 1, dict_缺失列[i]).value)
                else:
                    ws.cell(j, dict_缺失列[i]).value = 0
            else:
                if str(temp)[0] == "=":
                    ws.cell(j, dict_缺失列[i]).value = re.sub(r'\d+', str(j), temp)
                else:
                    ws.cell(j, dict_缺失列[i]).value = temp
            ws.cell(j, dict_缺失列[i]).alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    for i in range(1, ws.max_column + 1):
        ws.column_dimensions[get_column_letter(i)].width = ws_源.column_dimensions[get_column_letter(i)].width
        ws.column_dimensions[get_column_letter(i)].hidden = ws_源.column_dimensions[get_column_letter(i)].hidden
        ws.column_dimensions[get_column_letter(i)].max = ws_源.column_dimensions[get_column_letter(i)].max
        ws.column_dimensions[get_column_letter(i)].min = ws_源.column_dimensions[get_column_letter(i)].min

    for i in range(3, ws_源.max_row + 1):
        ws.row_dimensions[i].height = 13.5

    wb.save(新版底表路径[:-5] + "-" + datetime.datetime.now().strftime("%m.%d %H.%M") + ".xlsx")
    print("运行完成！")
    input("按任意键退出...")

    # for i in tqdm(list_缺失列, desc="添加缺失列"):
    #     ws.insert_cols(dict_列标_源[i])
    # wb.save(tools_pmc.lj_测试1)


数据源表路径, 新版底表路径 = 加载_自检程序()
print(数据源表路径, 新版底表路径)
main(数据源表路径, 新版底表路径)
